package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Page;
import com.xxxy.jsq.model.Works;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class WorksDao implements BaseDao<Works>{
    private C3P0Utils db = new C3P0Utils();

    public WorksDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(Works model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into pa_works(type_id, works_name, works_order, works_flag) ");
        sqlStr.append("values(?,?,?,?) ");

        conn = db.getConnection();
        // 检查作品名称是否重复（同一类型下）
        boolean flag = isSame(model, true);
        if (!flag) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setLong(1, model.getTypeId());
                pstm.setString(2, model.getWorksName());
                pstm.setInt(3, model.getWorksOrder() != null ? model.getWorksOrder() : 0);
                pstm.setString(4, model.getWorksFlag());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return num > 0 ? "Ok" : "Err";
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Works model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        // 检查作品名称是否重复（排除自身）
        if (isSame(model, false)) {
            return "Same";
        }

        sqlStr.append("update pa_works set ");
        if (model.getWorksName() != null && !model.getWorksName().equals("")) {
            sqlStr.append(" works_name='" + model.getWorksName() + "'");
        }
        if (model.getTypeId() != null) {
            sqlStr.append(" ,type_id=" + model.getTypeId());
        }
        if (model.getWorksOrder() != null) {
            sqlStr.append(" ,works_order=" + model.getWorksOrder());
        }
        if (model.getWorksFlag() != null && !model.getWorksFlag().equals("")) {
            sqlStr.append(" ,works_flag='" + model.getWorksFlag() + "'");
        }
        sqlStr.append(" where works_id=?");

        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getWorksId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }

    @Override
    public String delete(Works model) {
        return null;
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("delete from pa_works where works_id=? ");

        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }

    @Override
    public List<Works> getAllData(Works model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Works> list = new ArrayList<Works>();

        sqlStr.append(" SELECT * from pa_works ");
        sqlStr.append("where 1=1 ");
        if (model.getWorksName() != null && !model.getWorksName().equals("")) {
            sqlStr.append("and works_name='" + model.getWorksName() + "' ");
        }
        if (model.getTypeId() != null) {
            sqlStr.append(" and type_id=" + model.getTypeId() + " ");
        }
        if (model.getWorksFlag() != null && !model.getWorksFlag().equals("")) {
            sqlStr.append(" and works_flag='" + model.getWorksFlag() + "' ");
        }
        sqlStr.append(" ORDER BY works_order asc, works_id asc ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Works works = new Works();
                works.setWorksId(rs.getLong("works_id"));
                works.setTypeId(rs.getLong("type_id"));
                works.setWorksName(rs.getString("works_name"));
                works.setWorksOrder(rs.getInt("works_order"));
                works.setWorksFlag(rs.getString("works_flag").equals("0") ? "启用" : "禁用");
                list.add(works);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Works findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_works ");
        sqlStr.append("where 1=1 and works_id=? ");

        conn = db.getConnection();
        Works works = new Works();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            while (rs.next()) {
                works.setWorksId(rs.getLong("works_id"));
                works.setTypeId(rs.getLong("type_id"));
                works.setWorksName(rs.getString("works_name"));
                works.setWorksOrder(rs.getInt("works_order"));
                works.setWorksFlag(rs.getString("works_flag").equals("0") ? "启用" : "禁用");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return works;
    }

    @Override
    public Works find(Works model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_works ");
        sqlStr.append("where 1=1 ");

        if (model.getWorksName() != null && !model.getWorksName().equals("")) {
            sqlStr.append("and works_name='" + model.getWorksName() + "' ");
        }
        if (model.getTypeId() != null) {
            sqlStr.append(" and type_id=" + model.getTypeId() + " ");
        }
        if (model.getWorksFlag() != null && !model.getWorksFlag().equals("")) {
            sqlStr.append(" and works_flag='" + model.getWorksFlag() + "' ");
        }

        conn = db.getConnection();
        Works works = new Works();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                works.setWorksId(rs.getLong("works_id"));
                works.setTypeId(rs.getLong("type_id"));
                works.setWorksName(rs.getString("works_name"));
                works.setWorksOrder(rs.getInt("works_order"));
                works.setWorksFlag(rs.getString("works_flag").equals("0") ? "启用" : "禁用");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return works;
    }

    @Override
    public boolean isSame(Works model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        // 同一类型下作品名称不允许重复
        sqlStr.append(" select * from pa_works where works_name=? and type_id=? ");

        // 更新时排除自身ID
        if (!sflag) {
            sqlStr.append("and works_id!='" + model.getWorksId() + "' ");
        }

        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getWorksName());
            pstm.setLong(2, model.getTypeId());
            rs = pstm.executeQuery();
            flag = rs.next(); // 存在同名作品则返回true
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Works> getAllDataByPage(Works model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Works> list = new ArrayList<Works>();

        // 使用 JOIN 连接 pa_works 和 pa_type 表，关联条件是 type_id 相等
        sqlStr.append(" SELECT pw.*, pt.type_name ");
        sqlStr.append(" FROM pa_works pw ");
        sqlStr.append(" JOIN pa_type pt ON pw.type_id = pt.type_id ");
        sqlStr.append(" WHERE 1=1 ");

        // 拼接查询条件
        List<String> conditions = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        if (model.getWorksName() != null && !model.getWorksName().equals("")) {
            conditions.add(" pw.works_name LIKE ? ");
            params.add("%" + model.getWorksName() + "%");
        }
        if (model.getTypeId() != null) {
            conditions.add(" pw.type_id = ? ");
            params.add(model.getTypeId());
        }
        if (model.getWorksFlag() != null && !model.getWorksFlag().equals("")) {
            conditions.add(" pw.works_flag = ? ");
            params.add(model.getWorksFlag());
        }

        // 将条件拼接到 SQL 中
        if (!conditions.isEmpty()) {
            sqlStr.append(" AND ");
            sqlStr.append(String.join(" AND ", conditions));
        }

        sqlStr.append(" ORDER BY pw.works_order asc, pw.works_id asc ");
        sqlStr.append(" LIMIT ?, ? ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            // 设置条件参数
            int paramIndex = 1;
            for (Object param : params) {
                if (param instanceof String) {
                    pstm.setString(paramIndex++, (String) param);
                } else if (param instanceof Long) {
                    pstm.setLong(paramIndex++, (Long) param);
                }
            }
            // 设置分页参数
            pstm.setInt(paramIndex++, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(paramIndex, page.getCount());

            rs = pstm.executeQuery();

            while (rs.next()) {
                Works works = new Works();
                works.setWorksId(rs.getLong("works_id"));
                works.setTypeId(rs.getLong("type_id"));
                works.setWorksName(rs.getString("works_name"));
                works.setWorksOrder(rs.getInt("works_order"));
                works.setWorksFlag(rs.getString("works_flag").equals("0") ? "启用" : "禁用");
                // 获取关联的类型名称并设置到 Works 对象中
                works.setTypeName(rs.getString("type_name"));
                list.add(works);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;

    }

    @Override
    public int getCount(Works model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select count(*) from pa_works ");
        sqlStr.append("where 1=1 ");

        if (model.getWorksName() != null && !model.getWorksName().equals("")) {
            sqlStr.append("and works_name='" + model.getWorksName() + "' ");
        }
        if (model.getTypeId() != null) {
            sqlStr.append(" and type_id=" + model.getTypeId() + " ");
        }
        if (model.getWorksFlag() != null && !model.getWorksFlag().equals("")) {
            sqlStr.append(" and works_flag='" + model.getWorksFlag() + "' ");
        }

        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Works> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Works> list = new ArrayList<Works>();

        sqlStr.append("select * from pa_works where works_flag='0' "); // 0表示启用
        sqlStr.append("order by works_order asc, works_name asc ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Works works = new Works();
                works.setWorksId(rs.getLong("works_id"));
                works.setWorksName(rs.getString("works_name"));
                list.add(works);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
